package org.robot.dao;

import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.jsoup.helper.StringUtil;
import org.robot.config.Config;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class BaseDao {
	private static Log log = LogFactory.getLog(BaseDao.class);
	
    protected static QueryRunner qr;
    //获取单例的QueryRunner对象,该对象自动从连接池中获取连接，使用完后自动归还到连接池
    static {
        Config config = new Config();
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        dataSource.setJdbcUrl("jdbc:oracle:thin:@192.168.0.247:1521/robot");
        dataSource.setUser("oaspider");
        dataSource.setPassword("oaspider123457");
        dataSource.setMaxPoolSize(config.getThreadNumber() > dataSource.getMinPoolSize() ? config.getThreadNumber() : dataSource.getMinPoolSize());
        qr = new QueryRunner(dataSource);
    }
    
    /**
     * 调用此方法要保证实体类的字段和数据库表的列名相同
     * 表名是't_类名'
     * @param t 实体对象
     * @return 返回受影响的行数
     */
    public static <T> int save(T t) throws Exception {
        return save("t_" + t.getClass().getSimpleName(), t);
    }
    
    /**
     * 调用此方法要保证实体类的字段和数据库表的列名相同
     * @param tableName 如果表名不是't_类名'，请提供表名，否则调用不带表名的方法
     * @param t 实体对象
     * @return 返回受影响的行数
     */
    public static <T> int save(String tableName, T t) throws Exception {
        Class<?> clazz = t.getClass();
        List<String> columnList = new ArrayList<>();
        List<Object> valueList = new ArrayList<>();
        Field[] fieldArr = clazz.getDeclaredFields();
        for (int i = 0; i < fieldArr.length; i++) {
            Field field = fieldArr[i];
			field.setAccessible(true);
            //带有static或final的字段或空值不要
            int mod = field.getModifiers();
            Object value = field.get(t);
            if (Modifier.isStatic(mod) || Modifier.isFinal(mod) || value == null)
                continue;
            columnList.add(field.getName());
            valueList.add(value);
        }
        Long pid = selectSequence(clazz);
        //添加主键
        columnList.add("pid");
        valueList.add(pid);
        String columnSql = String.join(",", columnList);
        String delimiterSql = columnList.stream().map(col -> "?").collect(Collectors.joining(","));
        String sql = "insert into " + tableName + "(" + columnSql + ") values(" + delimiterSql + ")";
        log.debug("执行sql：" + sql + " 参数：" + valueList.toString());
        return qr.update(sql, valueList.toArray());
    }

    private static Long selectSequence(Class<?> clazz) throws Exception {
        return Long.parseLong(qr.query("select " + clazz.getSimpleName() + "_SEQUENCE.nextval from dual", new ScalarHandler<>()).toString());
    }
    
    public static Long selectPidByWebsite(Class<?> t,String website, Long taskid) {
        String sql = "select pid from t_" + t.getSimpleName() + " where website = ? and taskid = ?";
        try {
            BigDecimal pid = qr.query(sql, new ScalarHandler<>(), website, taskid);
            if (pid != null)
			return Long.valueOf(pid.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    
    /**
     * 根据条件查询
     * @author pdz
     * @date 2018年12月6日
     * @param t 类型，如Journal.class
     * @param wherePartSql 查询条件，可以为空，如果不为空需要使用占位符，以where开头，<br>
     * 如where doi not is null and doi != '' and taskid = ?
     * @param params 前一个参数占位符的值
     * @return
     */
    public static <T> List<T> list(Class<T> t, String wherePartSql, Object... params) {
    	wherePartSql = StringUtil.isBlank(wherePartSql) ? "" : " " + wherePartSql;
    	String sql = "select * from t_" + t.getSimpleName() +  wherePartSql;
    	try {
			return qr.query(sql, new BeanListHandler<>(t), params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	return null;
    }
    
    /**
     * 根据taskid和website2个字段修改
     * @author pdz
     * @date 2018年12月5日
     * @param t
     * @return
     * @throws Exception
     */
    public static <T> int update(T t) throws Exception {
        return update("t_" + t.getClass().getSimpleName(), t);
    }
    
    public static <T> int update(String tableName, T t) throws Exception {
        Class<?> clazz = t.getClass();
        List<String> columnList = new ArrayList<>();
        List<Object> valueList = new ArrayList<>();
        Field[] fieldArr = clazz.getDeclaredFields();
        Long taskid = null;
        String website = null;
        for (int i = 0; i < fieldArr.length; i++) {
            fieldArr[i].setAccessible(true);
            //带有static或final的字段或空值不要
            int mod = fieldArr[i].getModifiers();
            Object value = fieldArr[i].get(t);
            if (Modifier.isStatic(mod) || Modifier.isFinal(mod) || value == null)
                continue;
            String columnName = fieldArr[i].getName();
            if (columnName.equalsIgnoreCase("taskid"))
            	taskid = (Long) value;
            else if (columnName.equalsIgnoreCase("website"))
            	website = value.toString();
            else {
            	columnList.add(columnName);
            	valueList.add(value);
            }
        }
        valueList.add(taskid);
        valueList.add(website);
        String setSql = IntStream.range(0, columnList.size()).mapToObj(i -> columnList.get(i) + "= ?").collect(Collectors.joining(","));
        String sql = "update " + tableName + " set " + setSql + " where taskid = ? and website = ?";
        log.debug("执行sql：" + sql + " 参数：" + valueList.toString());
        return qr.update(sql, valueList.toArray());
    }
}
